# -*- coding: utf-8 -*-
from nmj.sqlite.base_item import BaseNmjDB
from nmj.sqlite.tables import ALL_TABLES, Video, VideoSynopsis, DbVersion, Show, \
	VideoAttr, VideoGenre, VideoPerson, ScanDirs, ScanSystem, VideoGroup, \
	VideoGroupName
import logging
import os
import sqlite3

_LOGGER = logging.getLogger(__name__)
NMJ_SERIE_TYPE = 0
NMJ_MOVIE_TYPE = 3

NMJ_VIDEO_TITLE_TYPE_ATTR = 'TITLE_TYPE'
NMJ_VIDEO_GENRE_ATTR = 'GENRE'

NMJ_GENRE_DOCUMENTARY_OR_SHORT = 0
NMJ_GENRE_MOVIE = 1

DIRECTOR = "DIRECTOR"

# TODO: set this in a config file
ignored_sort_tokens = [
	u"à",
	u"le",
	u"la",
	u"l'",
	u"de",
	u"du",
	u"d'",
	u"the",
	u"an",
	u"a",
]

extension_to_system = {
	".mkv" : "Matroska",
	".avi" : "AVI",
}

class NMJDB(BaseNmjDB):
	def __init__(self, library_path):
		super(NMJDB, self).__init__(library_path)
		self.videos = Video.load(self.cursor)
		self.actors_cache = {}

	def purge(self):
		for table in Video, Show, VideoPerson, VideoGenre, VideoGroup, VideoSynopsis, VideoAttr:
			table.purge(self.cursor)
		self.connection.commit()

	def __insert_cast(self, movie):
		actors = movie.cast.get("actor", [])
		_LOGGER.debug("Cast contains %s actors", len(actors))
		cast_ids = []
		for actor in actors:
			actor_name = actor["name"]
			if actor_name in self.actors_cache:
				cast_ids.append("%s" % self.actors_cache[actor_name])
			else:
				matching_actor = VideoPerson.load(self.cursor, name=actor_name)
				if not matching_actor:
					video_person_id = VideoPerson.insert(self.cursor, name=actor_name)
					cast_ids.append("%s" % video_person_id)
					self.actors_cache[actor_name] = video_person_id
				else:
					cast_ids.append("%s" % matching_actor[0].video_person_id)
					self.actors_cache[actor_name] = matching_actor[0].video_person_id

		return ",".join(cast_ids)

	def __insert_director(self, movie, video_id, show_id):
		directors = movie.cast.get("director", "")
		_LOGGER.debug("Cast contains director %s", directors)
		if directors:
			for director in directors:
				VideoAttr.insert(self.cursor, video_id=video_id, show_id=show_id, type=DIRECTOR, value=director["name"])

	def _get_sortable_title(self, title):
		for prefix in ignored_sort_tokens:
			if title.startswith(prefix):
				return title[len(prefix):]
		return title

	def __insert_group(self, movie, video_db_id, show_db_id):
		sortable_title = self._get_sortable_title(movie.title)
		if not VideoGroupName.count(self.cursor, name=sortable_title[0].upper()):
			VideoGroupName.insert(self.cursor, name=sortable_title[0].upper(), language="FR")
		video_group_id = VideoGroupName.load(self.cursor, name=sortable_title[0].upper())[0].video_group_name_id
		VideoGroup.insert(
			self.cursor,
			video_group_name_id=video_group_id,
			video_id=video_db_id,
			show_id=show_db_id,
			show_type=movie.show_type,
			parental_rating=movie.rating)

	def __insert_genre(self, genre, video_db_id, show_db_id):
		VideoAttr.insert(self.cursor, video_id=video_db_id, show_id=show_db_id, type=NMJ_VIDEO_GENRE_ATTR, value=genre)
		# TODO: Find how determining if it is a short or a documentary to set the good type
		VideoGenre.insert(self.cursor, video_show_id=show_db_id, genre=genre, type=NMJ_GENRE_MOVIE)

	def insert_movie(self, movie):
		_LOGGER.debug("Inserting cast for movie %s", movie)
		cast = self.__insert_cast(movie)
		_LOGGER.debug("Inserting show for movie %s", movie)
		show_db_id = self.__insert_show(movie, cast)
		_LOGGER.debug("Inserting video for movie %s", movie)
		video_db_id = self.__insert_video(movie, cast, show_db_id)
		_LOGGER.debug("Inserting director for movie %s", movie)
		self.__insert_director(movie, video_db_id, show_db_id)
		_LOGGER.debug("Inserting group for movie %s", movie)
		self.__insert_group(movie, video_db_id, show_db_id)
		_LOGGER.debug("Updating show for movie %s", movie)
		self.update_show_total_item(show_db_id)
		_LOGGER.debug("Inserting genres for movie %s", movie)
		for genre in movie.genres:
			self.__insert_genre(genre, video_db_id, show_db_id)
		self.connection.commit()

	def update_show_total_item(self, show_db_id):
		show = Show.load(self.cursor, show_id=show_db_id)[0]
		nb_items = show.update_total_item(self.cursor)
		if nb_items == 0:
			show.delete(self.cursor)
		self.connection.commit()

	def search_and_delete(self, table, **kwargs):
		items = table.load(self.cursor, **kwargs)
		for item in items:
			item.delete(self.cursor)

	def __insert_video(self, movie, cast, show_db_id):
		video_db_id = Video.insert(
			self.cursor,
			path=movie.path,
			title=getattr(movie, "episode_title", None) or movie.title,
			search_title=movie.title,
			size=movie.size,
			ttid=movie.imdb_id,
			detail_poster=self.to_nmj_path(movie.poster.filepath),
			wallpaper=self.to_nmj_path(movie.fanart.filepath),
			update_state=3, # FIXME: Don't know what it means (probably "updated")
			cast_id=cast,
			show_id=show_db_id,
			season=getattr(movie, "season", ""),
			episode=getattr(movie, "episode", ""),
			release_date=getattr(movie, "release_date", "9999-01-01"),
			runtime=getattr(movie, "runtime", ""),
			rating=getattr(movie, "rating", ""),
		)
		VideoSynopsis.insert(self.cursor,
			content=movie.synopsis,
			video_id=video_db_id,
			show_id=show_db_id
		)
		VideoAttr.insert(
			self.cursor,
			video_id=video_db_id,
			type=NMJ_VIDEO_TITLE_TYPE_ATTR,
			value="feature",
		)
		return video_db_id

	def __insert_show(self, movie, cast):
		searching_constraints = {}
		if movie.imdb_id:
			searching_constraints["ttid"] = movie.imdb_id
		else:
			searching_constraints["title"] = movie.title
		matching_show = Show.load(self.cursor, **searching_constraints)
		if matching_show:
			return matching_show[0].show_id
		show_db_id = Show.insert(
			self.cursor,
			title=movie.title,
			search_title=movie.title,
			detail_poster=self.to_nmj_path(movie.poster.filepath),
			thumbnail=self.to_nmj_path(movie.poster.thumbpath),
			wallpaper=self.to_nmj_path(movie.fanart.filepath),
			ttid=movie.imdb_id,
			update_state=3, # FIXME: Don't know what it means (probably "updated")
			show_type=movie.show_type,
			cast_id=cast,
			release_date=getattr(movie, "release_date", "9999-01-01"),
			rating=getattr(movie, "rating", ""),
			system=extension_to_system.get(os.path.splitext(movie.path)[1], ""),
		)
		return show_db_id

	def create_db(self):
		_LOGGER.info("Creating database...")
		os.makedirs(os.path.dirname(self.media_db_path))
		conn = sqlite3.connect(self.media_db_path)
		conn.isolation_level = self.isolation_level
		cursor = conn.cursor()
		for table in ALL_TABLES:
			_LOGGER.debug("create table %s", table)
			table().create(cursor)
		DbVersion.insert(cursor, version="1.8.0")
		ScanDirs.insert(cursor, directory="LOCAL_DIR", name="LOCAL_NAME", scan_time="2010-11-11 13:11:42", size=1807172)
		ScanSystem.insert(cursor, type="RUNNING_STATUS", value="0")
		ScanSystem.insert(cursor, type="UPDATING_STAGE", value="5")
		conn.commit()
		cursor.close()
		conn.close()
		_LOGGER.info("Database creation done")


if __name__ == "__main__":
# 	import sys, pprint, re
	connection = sqlite3.connect("./sqlite/media.db") # give here the media.db
	connection.text_factory = str
	cursor = connection.cursor()
	# pprint.pprint(get_all_table_names(cursor))
	for table in ALL_TABLES:
		print 50 * "*", table
		items = table.load(cursor)
		for item in items:
			for attr in dir(item):
				if attr.startswith("__"):
					continue
				print "%s : %s" % (attr, getattr(item, attr))
	# fields = get_table_details(cursor, "VIDEO")[0][4].split("(")[1][:-1].split(", ")
	# pprint.pprint( "".join(['"%s", '%field.split(" ")[0] for field in fields]))
	# print get_DB_version(cursor)
	# print select(cursor, "SELECT TITLE, TOTAL_ITEM, SHOW_TYPE, TOTAL_EPISODES FROM SHOW")
	# videos = Video.load(cursor)
	# for attr in dir(videos[0]):
	# 	print "%s : %s" % (attr, getattr(videos[0], attr))
# 	for video in videos:
# 		print video, dir(video)

	# shows = Show.load(cursor)
	# for show in shows:
		# print show
